/***********************************************
 * This class is part of the database toolset
 * intended to be used with mySQL.  
 ***********************************************/

package database;

import gui.mainWindow;

import java.sql.*;

import javax.swing.JOptionPane;

/***********************************************
 * A class to manage the database connections.  
 * Connects to the 'workouts' database.
 * @author Brian Renzenbrink
 ***********************************************/
public class DB_connection{

	
	private String driver = "com.mysql.jdbc.Driver";
	private String url = "jdbc:mysql://localhost:3306/";
	private String database;
	private String username;
	private String password;
	private Connection conn;
	private Statement currentStatement;
	
	
	/******************************************************************
	 * A constructor to establish a connection to a default database
	 * using a default username and password.
	 * @param db
	 * @param pass 
	 * @param uName
	 ******************************************************************/
	public DB_connection() {
		
		database = "ExerciseManager";
		password = "dod6jake";
		username = "root";
		
		//Set the url equal to the desired database
		url = url + database;
		
		try{
			//Establish a connection to the database
			conn = DriverManager.getConnection(url, username, password);
			
			//Create a statement object
			currentStatement = conn.createStatement();
			
		}catch( Exception e ) {
			e.printStackTrace();
		}//end catch
	}//end DB_connection
	
	/******************************************************************
	 * A constructor to establish a connection to a given database
	 * using a given username and password.
	 * @param db
	 * @param pass 
	 * @param uName
	 ******************************************************************/
	public DB_connection(String db, String pass, String uName) {
		
		database = db;
		password = pass;
		username = uName;
		
		//Set the url equal to the desired database
		url = url + database;
		
		try{
			//Establish a connection to the database
			conn = DriverManager.getConnection(url, username, password);
			
			//Create a statement object
			currentStatement = conn.createStatement();
			
		}catch( Exception e ) {
			e.printStackTrace();
		}//end catch
	}//end DB_connection
	
	

	/******************************************************************
	 * @return the driver
	 ******************************************************************/
	public String getDriver() {
		return driver;
	}



	/******************************************************************
	 * @param driver the driver to set
	 ******************************************************************/
	public void setDriver(String driver) {
		this.driver = driver;
	}



	/******************************************************************
	 * @return the url
	 ******************************************************************/
	public String getUrl() {
		return url;
	}



	/******************************************************************
	 * @param url the url to set
	 ******************************************************************/
	public void setUrl(String url) {
		this.url = url;
	}



	/******************************************************************
	 * @return the database
	 ******************************************************************/
	public String getDatabase() {
		return database;
	}



	/******************************************************************
	 * @param database the database to set
	 ******************************************************************/
	public void setDatabase(String database) {
		this.database = database;
	}



	/******************************************************************
	 * @return the username
	 ******************************************************************/
	public String getUsername() {
		return username;
	}



	/******************************************************************
	 * @param username the username to set
	 ******************************************************************/
	public void setUsername(String username) {
		this.username = username;
	}



	/******************************************************************
	 * @return the password
	 ******************************************************************/
	public String getPassword() {
		return password;
	}



	/******************************************************************
	 * @param password the password to set
	 ******************************************************************/
	public void setPassword(String password) {
		this.password = password;
	}



	/******************************************************************
	 * @return the conn
	 ******************************************************************/
	public Connection getConn() {
		return conn;
	}



	/******************************************************************
	 * @param conn the conn to set
	 ******************************************************************/
	public void setConn(Connection conn) {
		this.conn = conn;
	}



	/******************************************************************
	 * @return the currentStatement
	 ******************************************************************/
	public Statement getCurrentStatement() {
		return currentStatement;
	}



	/******************************************************************
	 * @param currentStatement the currentStatement to set
	 ******************************************************************/
	public void setCurrentStatement(Statement currentStatement) {
		this.currentStatement = currentStatement;
	}
	
	/******************************************************************
	 * A method that returns the result set of a given SQL command on 
	 * the database connection.
	 * @param sql: The SQL command to execute
	 * @return rs: The resultset for the SQL command on the database
	 ******************************************************************/
	public ResultSet executeCurrentStatement(String sql) {
		ResultSet rs = null;
		
		try{
			rs = currentStatement.executeQuery(sql);
		} catch (SQLSyntaxErrorException e) {
			JOptionPane.showMessageDialog(null, "The following error was generated: " + e.getMessage()
					+ "\n\n You were trying to run the following SQL statement:\n" + sql, 
					"Error in syntax", JOptionPane.ERROR_MESSAGE);
		} catch (Exception e) {
			JOptionPane.showMessageDialog(null, "The following error was generated: " + e.getMessage()
					+ "\n\n You were trying to run the following SQL statement:\n" + sql, 
					"Error in syntax", JOptionPane.ERROR_MESSAGE);
			e.printStackTrace();
		}//end catch
		
		return rs;
	}//end executeCurrentStatement
	
	/******************************************************************
	 * A method that returns the result set of a given SQL update on 
	 * the database connection.
	 * @param sql: The SQL command to execute
	 * @return rs: The resultset for the SQL command on the database
	 ******************************************************************/
	public int executeCurrentUpdate(String sql) {
		int retVal = -1;
		
		try{
			retVal = currentStatement.executeUpdate(sql);
		} catch (SQLSyntaxErrorException e) {
			JOptionPane.showMessageDialog(null, "The following error was generated: " + e.getMessage()
					+ "\n\n You were trying to run the following SQL statement:\n" + sql, 
					"Error in syntax", JOptionPane.ERROR_MESSAGE);
		} catch (Exception e) {
			JOptionPane.showMessageDialog(null, "The following error was generated: " + e.getMessage()
					+ "\n\n You were trying to run the following SQL statement:\n" + sql, 
					"Error in syntax", JOptionPane.ERROR_MESSAGE);
			e.printStackTrace();
		}//end catch
		
		return retVal;
	}//end executeCurrentStatement

	/***********************************************************
	 * A method to close the current connection.
	 * @return true if the connection closes/is closed
	 ***********************************************************/
	public boolean closeConnection(){
		
		try{
			if(conn.isClosed()){
				System.out.println("Connection already closed.");
			}
			else{
				conn.close();
			}
		}
		catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		System.out.println("Connection closed.");
		return true;
	}
	
	
	public static void main(String args[]){
		 createUserAndDB();
//		 editDB();
//		 removeUserAndDB();
		  //testWorkouts();
	}
	  
	  public static void testWorkouts(){
		  System.out.println("Editing a database");
		  try {
			  Statement stmt;
			  ResultSet rs;
			
			  //Register the JDBC driver for MySQL.
			  Class.forName("com.mysql.jdbc.Driver");
			
			  //Define URL of database server for
			  // database named JunkDB on the localhost
			  // with the default port number 3306.
			  String url ="jdbc:mysql://localhost:3306/workouts";
			
			  //Get a connection to the database for a
			  // user named auser with the password
			  // drowssap, which is password spelled
			  // backwards.
			  Connection con = DriverManager.getConnection(url,"root", "xenogears");
			
			  //Display URL and connection information
			  System.out.println("URL: " + url);
			  System.out.println("Connection: " + con);
			
			  //Get a Statement object
			  stmt = con.createStatement();
			  
			//Query the database, storing the result
			// in an object of type ResultSet
			rs = stmt.executeQuery("SELECT * " + "from weightlifting ORDER BY Date");
			  
			//Use the methods of class ResultSet in a
			  // loop to display all of the data in the
			  // database.
			  System.out.println("Display all results:");
			  int count = 1;
			  while(rs.next()){
				  Date date = rs.getDate("Date");
				  String str = rs.getString("Exercise");
				  System.out.println(count + ":\tDate= " + date.toString() + "\tExercise = " + str);
				  count++;
			  }//end while loop
			  
			  con.close();
		  }catch( Exception e ) {
			  e.printStackTrace();
		  }//end catch
	  }
	  public static void createUser(String database, String username){
		  System.out.println("Creating User on Database: " + database);
		  try {
	      Statement stmt;

	      //Register the JDBC driver for MySQL.
	      Class.forName("com.mysql.jdbc.Driver");

	      //Define URL of database server for
	      // database named mysql on the localhost
	      // with the default port number 3306.
	      String url ="jdbc:mysql://localhost:3306/mysql";

	      //Get a connection to the database for a
	      // user named root with a password.
	      // This user is the default administrator
	      // having full privileges to do anything.
	      Connection con = DriverManager.getConnection(url,"root", "xenogears");

	      //Display URL and connection information
	      System.out.println("URL: " + url);
	      System.out.println("Connection: " + con);

	      //Get a Statement object
	      stmt = con.createStatement();

	      //Create the new database
	      //stmt.executeUpdate("CREATE DATABASE JunkDB");
	      
	      //Register a new user named auser on the
	      // database named JunkDB with a password
	      // drowssap enabling several different
	      // privileges.
	      stmt.executeUpdate("GRANT SELECT,INSERT,UPDATE,DELETE," + "CREATE,DROP " + "ON " + database + ".* TO '" + username + "'@'localhost' " + "IDENTIFIED BY 'drowssap';");
	      con.close();
		  }catch( Exception e ) {
			  e.printStackTrace();
		  }//end catch
	  }
	  
	  
	  public static void createUserAndDB(){
		  System.out.println("Creating User and Database");
		  try {
	      Statement stmt;

	      //Register the JDBC driver for MySQL.
	      Class.forName("com.mysql.jdbc.Driver");

	      //Define URL of database server for
	      // database named mysql on the localhost
	      // with the default port number 3306.
	      String url ="jdbc:mysql://localhost:3306/mysql";

	      //Get a connection to the database for a
	      // user named root with a password.
	      // This user is the default administrator
	      // having full privileges to do anything.
	      Connection con =DriverManager.getConnection(url,"root", "xenogears");

	      //Display URL and connection information
	      System.out.println("URL: " + url);
	      System.out.println("Connection: " + con);

	      //Get a Statement object
	      stmt = con.createStatement();

	      //Create the new database
	      stmt.executeUpdate("CREATE DATABASE IF NOT EXISTS JunkDB");
	      
	      //Register a new user named auser on the
	      // database named JunkDB with a password
	      // drowssap enabling several different
	      // privileges.
	      stmt.executeUpdate("GRANT SELECT,INSERT,UPDATE,DELETE," + "CREATE,DROP " + "ON JunkDB.* TO 'auser'@'localhost' " + "IDENTIFIED BY 'drowssap';");
	      con.close();
		  }catch( Exception e ) {
			  e.printStackTrace();
		  }//end catch
	  }//end function
	 
	  public static void removeUserAndDB(){
		  System.out.println("Removing User AND Database");
		  try {
			  Statement stmt;
			
			  //Register the JDBC driver for MySQL.
			  Class.forName("com.mysql.jdbc.Driver");
			
			  //Define URL of database server for
			  // database named mysql on the localhost
			  // with the default port number 3306.
			  String url ="jdbc:mysql://localhost:3306/mysql";
			
			  //Get a connection to the database for a
			  // user named root with a password.
			  // This user is the default administrator
			  // having full privileges to do anything.
			  Connection con = DriverManager.getConnection(url,"root", "xenogears");
			
			  //Display URL and connection information
			  System.out.println("URL: " + url);
			  System.out.println("Connection: " + con);
			
			  //Get a Statement object
			  stmt = con.createStatement();
			
			  //Remove the user named auser
			  stmt.executeUpdate("REVOKE ALL PRIVILEGES ON *.* " + "FROM 'auser'@'localhost'");
			  stmt.executeUpdate("REVOKE GRANT OPTION ON *.* " + "FROM 'auser'@'localhost'");
			  stmt.executeUpdate("DELETE FROM mysql.user WHERE " + "User='auser' and Host='localhost'");
			  stmt.executeUpdate("FLUSH PRIVILEGES");
			
			  //Delete the database
			  stmt.executeUpdate("DROP DATABASE JunkDB");
			
			  con.close();
		  }catch( Exception e ) {
			  e.printStackTrace();
		  }//end catch
		}//end function

	  public static void editDB(){
		  System.out.println("Editing a database");
		  try {
			  Statement stmt;
			  ResultSet rs;
			
			  //Register the JDBC driver for MySQL.
			  Class.forName("com.mysql.jdbc.Driver");
			
			  //Define URL of database server for
			  // database named JunkDB on the localhost
			  // with the default port number 3306.
			  String url ="jdbc:mysql://localhost:3306/JunkDB";
			
			  //Get a connection to the database for a
			  // user named auser with the password
			  // drowssap, which is password spelled
			  // backwards.
			  Connection con = DriverManager.getConnection(url,"auser", "drowssap");
			
			  //Display URL and connection information
			  System.out.println("URL: " + url);
			  System.out.println("Connection: " + con);
			
			  //Get a Statement object
			  stmt = con.createStatement();
			
			  //As a precaution, delete myTable if it
			  // already exists as residue from a
			  // previous run.  Otherwise, if the table
			  // already exists and an attempt is made
			  // to create it, an exception will be
			  // thrown.
			  try{
				  stmt.executeUpdate("DROP TABLE myTable");
			  }catch(Exception e){
				  System.out.print(e);
				  System.out.println("No existing table to delete");
			  }//end catch
			
			  //Create a table in the database named
			  // myTable.
			  stmt.executeUpdate("CREATE TABLE myTable(test_id int," + "test_val char(15) not null)");
			
			  //Insert some values into the table
			  stmt.executeUpdate("INSERT INTO myTable(test_id, " + "test_val) VALUES(1,'One')");
			  stmt.executeUpdate("INSERT INTO myTable(test_id, " + "test_val) VALUES(2,'Two')");
			  stmt.executeUpdate("INSERT INTO myTable(test_id, " + "test_val) VALUES(3,'Three')");
			  stmt.executeUpdate("INSERT INTO myTable(test_id, " + "test_val) VALUES(4,'Four')");
			  stmt.executeUpdate("INSERT INTO myTable(test_id, " + "test_val) VALUES(5,'Five')");
			
			  //Get another statement object initialized
			  // as shown.
			  stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			
			  //Query the database, storing the result
			  // in an object of type ResultSet
			  rs = stmt.executeQuery("SELECT * " + "from myTable ORDER BY test_id");
			
			  //Use the methods of class ResultSet in a
			  // loop to display all of the data in the
			  // database.
			  System.out.println("Display all results:");
			  while(rs.next()){
				  int theInt= rs.getInt("test_id");
				  String str = rs.getString("test_val");
				  System.out.println("\ttest_id= " + theInt + "\tstr = " + str);
			  }//end while loop
			
			  //Display the data in a specific row using
			  // the rs.absolute method.
			  System.out.println("Display row number 2:");
			  if( rs.absolute(2) ){
				  int theInt= rs.getInt("test_id");
				  String str = rs.getString("test_val");
				  System.out.println("\ttest_id= " + theInt + "\tstr = " + str);
			  }//end if
			
			  //Delete the table and close the connection
			  // to the database
			  stmt.executeUpdate("DROP TABLE myTable");
			  con.close();
		  }catch( Exception e ) {
			  e.printStackTrace();
		  }//end catch
	  }//end function
  
}


